package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBSales implements IFDBGeneric<Sales> {
	private Connection con;
	private DBLineItem dbLineItem;

	public DBSales()
	{
		con = DbConnection.getInstance().getDBcon();
		dbLineItem = new DBLineItem();
	}
	
	
	
	public ArrayList<Sales> getAll() {
		return  miscWhere("");
	}

	public ArrayList<Sales> getAll_byCustomerID(int cid){
		return miscWhere("cid=" +cid);
	}
	
	
	public Sales find(int invoiceNo) {
		return singleWhere("InvoiceNo = "+invoiceNo);
	}
	
	public Sales find(String name) {return null;}
	

	public int delete(int invoiceNo)
	{
		int rc = -1;
		
		String query = "DELETE FROM SalesRental WHERE invoiceNo = "+invoiceNo;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			dbLineItem.delete(invoiceNo);
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the sale with Invoice "+invoiceNo);
		}
		
		
		return rc;
	}
	
	
	public int insert(Sales sale) throws Exception 
	{
		int rc = -1;
		
		String query1 = "INSERT INTO SalesRental (date,amount,cid) VALUES ('"+sale.getDate()+"',"+sale.getAmount()+","+sale.getCid()+")";

		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = stmt.getGeneratedKeys();
	        if (rs.next())  rc = rs.getInt(1);
	        rs.close();
	        
	        
			if(rc > 0)
			{
				String query2 = "INSERT INTO Sales (invoiceNo,deliveryStatus,deliveryDate) VALUES "
					+ "("+rc+",'"+sale.getDeliveryStatus()+"','"+sale.getDeliveryDate()+"')";
				
				rc = stmt.executeUpdate(query2);
				
				for(LineItem ltime : sale.getProducts())
				{
					ltime.setInvoiceNo(rc);
					dbLineItem.insert(ltime);
				}
				
				
			}
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't insert the sale with invoice "+sale.getInvoiceNo());
		}
		
		
		return rc;
	}

	
	public int update(Sales sale) 
	{
		int rc = -1;
		
		String query1 = "UPDATE SalesRental SET date = '"+sale.getDate()+"',amount = "+sale.getAmount()+", Cid = "+sale.getCid()+" WHERE invoiceNo = "+sale.getInvoiceNo();
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			if(rc > 0)
			{
				String query2 = "UPDATE Sales SET deliveryDate = '"+sale.getDeliveryDate()+"',deliveryStatus = '"+sale.getDeliveryStatus()+"' WHERE invoiceNo = "+sale.getInvoiceNo();
				rc = stmt.executeUpdate(query2);
				
				for(LineItem ltime : sale.getProducts())
				{
					dbLineItem.update(ltime);
				}
			}
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update the Sale with invoice Number "+sale.getInvoiceNo());
		}
		
		
		return rc;
	}
	
	
	
	
	
	
	
	
	private Sales singleWhere(String wClause)
	{
		Sales sale =null;
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				sale = buildSale(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return sale;
	}
	
	
	
	private ArrayList<Sales> miscWhere(String wClause)
	{
		ArrayList<Sales> sales = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				Sales sale = buildSale(results);
				sales.add(sale);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		
		
		
		
		return sales;
	}
	
	
	
	
	
	
	private Sales buildSale(ResultSet result)
	{
		Sales sales = new Sales();
		try{	
			sales.setInvoiceNo(result.getInt("invoiceNo"));
			sales.setDeliveryDate(result.getDate("DeliveryDate"));
			sales.setDeliveryStatus(result.getString("DeliveryStatus"));
			sales.setDate(result.getDate("Date"));
			sales.setAmount(result.getDouble("Amount"));
			sales.setCid(result.getInt("Cid"));
			
			ArrayList<LineItem> lineItems = dbLineItem.getAll_byInvoiceNo(sales.getInvoiceNo());
			sales.setLineItem(lineItems);
				
		}
		catch(Exception ex){ System.err.println("Unable to build a Sale ");} 
		
		return sales;
	}
	
	
	private String buildQuery(String wClause)
	{
		String query = "Select invoiceNo, date, amount, cid, deliverystatus, deliveryDate FROM SalesRental sr, Sales s WHERE sr.invoiceNo=s.invoiceNo";
		
		if(wClause.length() > 0) query = query + " AND " + wClause;
		
		System.out.println(query);
		
		return query;
	}


}

